﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Yunlib.Entity;
using YunLib.Common;
using Yunlib.Common;

namespace Yunlib.DAL
{
    public class ReturnOrBrDAL
    {
        SqlConnection conn = new SqlConnection(SqlServerHelper.ConnectionString);
        /// <summary>
        /// 通过条形码获取书和借阅信息详情
        /// </summary>
        /// <returns></returns>
        public BooksInfo GetBooksInfoByBarcode(string barCode)
        {
            #region 1
            StringBuilder strSql = new StringBuilder();
            //申明读者条码,允许续借次数,续借期限
            strSql.AppendLine(@" DECLARE @RDID varchar(50),");
            strSql.AppendLine(@"         @AllowOnceBorrowCount int,");
            strSql.AppendLine(@"         @OnceBorrowTerm int,");
            strSql.AppendLine(@"         @BorrowCountNow int");

            //通过书的条形码查询出读者条码
            strSql.AppendLine(@" SELECT @RDID= d.读者条码");
            strSql.AppendLine(@" FROM [馆藏书目库] a");
            strSql.AppendLine(@" LEFT JOIN [采购库] b on a.主键码 = b.主键码");
            strSql.AppendLine(@" LEFT JOIN [馆藏典藏库] c on b.子键码 = c.子键码");
            strSql.AppendLine(@" LEFT JOIN [流通库] d on d.条形码=c.条形码");
            strSql.AppendFormat(@" WHERE c.条形码='{0}'".FormatWith(barCode));

            //查询当前已续借次数
            strSql.AppendLine(@" SELECT @BorrowCountNow=COUNT(*) FROM 流通日志");
            strSql.AppendLine(@" WHERE 读者条码= @RDID");
            strSql.AppendLine(@" AND 操作类型='X'");

            //通过读者条码查询出该读者允许续借的次数和期限
            strSql.AppendLine(@" SELECT DISTINCT @AllowOnceBorrowCount=a.允许续借次数,");
            strSql.AppendLine(@"                 @OnceBorrowTerm=a.续借期限");
            strSql.AppendLine(@" FROM [流通参数定义] a");
            strSql.AppendLine(@" LEFT JOIN [读者库] b on a.单位代码=b.单位代码");
            strSql.AppendLine(@" AND a.级别代码=b.级别代码");
            strSql.AppendLine(@" WHERE b.读者条码=@RDID");

            strSql.AppendLine(@" SELECT DISTINCT a.题名 as BooksName,");
            strSql.AppendLine(@"        e.姓名 as ReaderName,");
            strSql.AppendLine(@"        e.级别代码 as ClassCode, ");
            strSql.AppendLine(@"        e.单位代码 as CellCode,");
            strSql.AppendLine(@"        e.可外借 as CanBorrowCount,");
            strSql.AppendLine(@"        e.已外借 as BorrowEDCount,");
            strSql.AppendLine(@"        @AllowOnceBorrowCount as AllowOnceBorrowCount,");
            strSql.AppendLine(@"        @OnceBorrowTerm as OnceBorrowTerm,");
            strSql.AppendLine(@"        @BorrowCountNow as BorrowCountNow,");
            strSql.AppendLine(@"        a.索书号 as SuoBookNum,");
            strSql.AppendLine(@"        c.条形码 as BarCode,");
            strSql.AppendLine(@"        c.主键码 as ID,");
            strSql.AppendLine(@"        d.读者条码 as ReaderCode,");
            strSql.AppendLine(@"        d.外借时间 as BorrowDate,");
            strSql.AppendLine(@"        d.应归还时间 as ShouldReturnDate");
            strSql.AppendLine(@" FROM [馆藏书目库] a");
            strSql.AppendLine(@" LEFT JOIN [采购库] b on a.主键码 = b.主键码");
            strSql.AppendLine(@" LEFT JOIN [馆藏典藏库] c on b.子键码 = c.子键码");
            strSql.AppendLine(@" LEFT JOIN [流通库] d on d.条形码=c.条形码");
            strSql.AppendLine(@" LEFT JOIN [读者库] e on e.读者条码=d.读者条码");
            strSql.AppendLine(@" LEFT JOIN [流通日志] f on  f.读者条码=d.读者条码");
            strSql.AppendFormat(@" WHERE c.条形码='{0}'".FormatWith(barCode));

            BooksInfo model = new BooksInfo();

            //获取数据
            IDataReader dr = SqlServerHelper.ExecuteReader(strSql.ToString(), conn);

            if (dr.Read())
            {
                model.ReaderName = dr["ReaderName"].ToString();
                model.BorrowCountNow = Convert.ToInt16(dr["BorrowCountNow"].ToString());
                model.CanBorrowCount = (dr["CanBorrowCount"].ToString() == "") ? 0 : Convert.ToInt32(dr["CanBorrowCount"].ToString());
                model.BorrowCountED = (dr["BorrowEDCount"].ToString() == "") ? (short)0 : Convert.ToInt16(dr["BorrowEDCount"].ToString());
                model.AllowOnceBorrowCount = (dr["AllowOnceBorrowCount"].ToString() == "") ? (short)0 : Convert.ToInt16(dr["AllowOnceBorrowCount"].ToString());
                model.OnceBorrowTerm = (dr["OnceBorrowTerm"].ToString() == "") ? (short)0 : Convert.ToInt16(dr["OnceBorrowTerm"].ToString());
                model.BooksName = dr["BooksName"].ToString();
                model.SuoBookNum = dr["SuoBookNum"].ToString();
                model.BarCode = dr["BarCode"].ToString();
                model.ID = Convert.ToInt32(dr["ID"].ToString());
                model.ReaderCode = dr["ReaderCode"].ToString();
                model.BorrowDate = (dr["BorrowDate"].ToString() == "") ? DateTime.Parse("1990-1-1") : Convert.ToDateTime(dr["BorrowDate"].ToString());
                model.ShouldReturnDate = (dr["ShouldReturnDate"].ToString() == "") ? DateTime.Parse("1990-1-1") : Convert.ToDateTime(dr["ShouldReturnDate"].ToString());
            }
            conn.Close();
            return model;
            #endregion

        }

        public int GetBookIDByBarCode(string barCode)
        {
            try
            {
                string sql = @"SELCET 条形码 as BOOKCODE FROM 馆藏典藏库 WHERE 条形码 = '{0}'".FormatWith(barCode);

                DataTable dt = SqlServerHelper.ExecuteDataTable(sql);

                return int.Parse(dt.Rows[0][0].ToString());
            }catch(Exception ex)
            {
                return 0;
            }

        }

        public DataTable GetNewBooksInfoByBarcode(string barCode)
        {
            var sqlstr = @" SELECT a.条形码 as BOOKCODE,a.刊价 as PRICE,substring(b.索书号,1,1) as CLASSIFICATION_NUM, b.题名 as TITLE, b.责任者 as AUTHOR, b.出版者 as PUBLISHER,b.出版地 as PUBLISHERPLACE, b.出版日期 as PUBDATE, b.标准编码 as ISBN, b.语种 as LAGS, b.图象页数 as PAGES, b.版次 as EDITION,a.虚拟库室 as XUNI FROM  馆藏典藏库 as a  left join 馆藏书目库 as b on a.主键码 = b.主键码 where a.条形码 = '{0}'".FormatWith(barCode);
            DataTable dt = SqlServerHelper.ExecuteDataTable(sqlstr.ToString());

            return dt;
        }

        public DataTable GetNewBooksInfoByPage(string searchType,string searchStr,int page,int pageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" SELECT Top({0}) a.条形码 as BOOKCODE,a.刊价 as PRICE,substring(b.索书号,1,1) as CLASSIFICATION_NUM, b.题名 as TITLE, b.责任者 as AUTHOR, b.出版者 as PUBLISHER,b.出版地 as PUBLISHERPLACE, 
	b.出版日期 as PUBDATE, b.标准编码 as ISBN, b.语种 as LAGS, b.图象页数 as PAGES, b.版次 as EDITION FROM 馆藏典藏库 as a 
	 left join 馆藏书目库 as b on a.主键码 = b.主键码 where".FormatWith(pageSize));
            if(searchType == "1")
            {
                strSql.AppendLine(@" b.题名 like '%{0}%'".FormatWith(searchStr));
            }else if (searchType == "2")
            {
                strSql.AppendLine(@" b.责任者 like '%{0}%'".FormatWith(searchStr));
            }
            else if (searchType == "3")
            {
                strSql.AppendLine(@" b.标准编码 like '%{0}%'".FormatWith(searchStr));
            }
            else if (searchType == "4")
            {
                strSql.AppendLine(@" b.索书号 like '%{0}%'".FormatWith(searchStr));
            }

            strSql.AppendLine(@" AND a.条形码 not in (SELECT c.条形码  FROM 流通库 c left join 馆藏典藏库 as a on c.条形码 = a.条形码
	 left join 馆藏书目库 as b on a.主键码 = b.主键码 WHERE ");
            if (searchType == "1")
            {
                strSql.AppendLine(@" b.题名 like '%{0}%' )".FormatWith(searchStr));
            }
            else if (searchType == "2")
            {
                strSql.AppendLine(@" b.责任者 like '%{0}%' )".FormatWith(searchStr));
            }
            else if (searchType == "3")
            {
                strSql.AppendLine(@" b.标准编码 like '%{0}%' )".FormatWith(searchStr));
            }
            else if (searchType == "4")
            {
                strSql.AppendLine(@" b.索书号 like '%{0}%' )".FormatWith(searchStr));
            }
           
            DataTable dt = SqlServerHelper.ExecuteDataTable(strSql.ToString());

            return dt;
        }

        public DataTable GetUserLendByDzCode(string dzCode)
        {
            var sqlstr = @" SELECT a.条形码 as BOOKCODE,a.刊价 as PRICE,substring(b.索书号,1,1) as CLASSIFICATION_NUM, b.题名 as TITLE, b.责任者 as AUTHOR, b.出版者 as PUBLISHER,b.出版地 as PUBLISHERPLACE, b.出版日期 as PUBDATE, b.标准编码 as ISBN, b.语种 as LAGS, b.图象页数 as PAGES, b.版次 as EDITION FROM 流通库 c left join 馆藏典藏库 as a on c.条形码 = a.条形码 left join 馆藏书目库 as b on a.主键码 = b.主键码 where c.读者条码 = '{0}'".FormatWith(dzCode);
            DataTable dt = SqlServerHelper.ExecuteDataTable(sqlstr.ToString());

            return dt;
        }
        /// <summary>
        /// 添加记录到流通日志中
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertLTKOneInfo(ChangeLogInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" INSERT INTO [流通日志]");
            strSql.AppendLine(@"        (操作类型,");
            strSql.AppendLine(@"         条形码,");
            strSql.AppendLine(@"         登录号,");
            strSql.AppendLine(@"         读者条码,");
            strSql.AppendLine(@"         处理时间,");
            strSql.AppendLine(@"         赔罚款,");
            strSql.AppendLine(@"         操作员,");
            strSql.AppendLine(@"         主键码,");
            strSql.AppendLine(@"         流通库室键码,");
            strSql.AppendLine(@"         签到键码");
            strSql.AppendLine(@"         )");
            strSql.AppendLine(@" VALUES");
            strSql.AppendFormat(@"       ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7},{8},{9})", model.OperateType, model.BarCode, model.LoginAccount, model.ReaderCode, model.DealTime, model.Penalty, model.Operator, model.ID, model.LTKID, model.QDID);

            return SqlServerHelper.ExecuteNonQuery(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 通过条形码获取未还书籍
        /// </summary>
        /// <returns></returns>
        public ChangeKuInfo GetBorrowBooksInfoByBarcode(string barCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" SELECT [条形码] as BarCode,");
            strSql.AppendLine(@"        [登录号] as LoginAccount,");
            strSql.AppendLine(@"        [读者条码] as ReaderCode,");
            strSql.AppendLine(@"        [外借时间] as BorrowTime,");
            strSql.AppendLine(@"        [应归还时间] as ShouldReturnTime,");
            strSql.AppendLine(@"        [主键码] as ID,");
            strSql.AppendLine(@"        [虚拟库室] as DummyKu,");
            strSql.AppendLine(@"        [续借次数] as OnceBorrowCount,");
            strSql.AppendLine(@"        [处理时间] as DealTime,");
            strSql.AppendLine(@"        [流通库室键码] as LTKSID,");
            strSql.AppendLine(@"        [签到键码] as QDID");
            strSql.AppendLine(@" FROM [流通库]");
            strSql.AppendFormat(@" WHERE [条形码]='{0}'".FormatWith(barCode));

            IDataReader dr = SqlServerHelper.ExecuteReader(strSql.ToString(), conn);

            ChangeKuInfo model = null;

            if (dr.Read())
            {
                model = new ChangeKuInfo();
                model.BarCode = dr["BarCode"].ToString();
                model.LoginAccount = dr["LoginAccount"].ToString();
                model.ReaderCode = dr["ReaderCode"].ToString();
                model.BorrowTime = Convert.ToDateTime(dr["BorrowTime"].ToString());
                model.ShouldReturnTime = Convert.ToDateTime(dr["ShouldReturnTime"].ToString());
                model.ID = Convert.ToInt32(dr["ID"].ToString());
                model.DummyKu = Convert.ToInt32(dr["DummyKu"].ToString());
                model.OnceBorrowCount = Convert.ToInt32(dr["OnceBorrowCount"].ToString());
            }

            conn.Close();
            return model;
        }

        public DataTable GetChangeInfoByBarcode(string barCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" SELECT [条形码] as BARCODE ,");
            strSql.AppendLine(@"        [登录号] as LOGIN ,");
            strSql.AppendLine(@"        [读者条码] as DZCODE  ,");
            strSql.AppendLine(@"        [外借时间] as CHECKTIME ,");
            strSql.AppendLine(@"        [应归还时间] as RETURNTIME ,");
            strSql.AppendLine(@"        [主键码] as PRIMARYKEY,");
            strSql.AppendLine(@"        [虚拟库室] as VIRTUALLIBRARYROOM,");
            strSql.AppendLine(@"        [续借次数] as RENEWNUMBER ,");
            strSql.AppendLine(@"        [处理时间] as PROCESSINGTIME ,");
            strSql.AppendLine(@"        [流通库室键码] as LENDKEY ,");
            strSql.AppendLine(@"        [签到键码] as SIGNIN");
            strSql.AppendLine(@" FROM [流通库]");
            strSql.AppendFormat(@" WHERE [条形码]='{0}'".FormatWith(barCode));

            DataTable dt = SqlServerHelper.ExecuteDataTable(strSql.ToString());

            
            return dt;
        }

        public DataTable GetChangeInfoByDzCode(string dzCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" SELECT [条形码] as BARCODE ,");
            strSql.AppendLine(@"        [登录号] as LOGIN ,");
            strSql.AppendLine(@"        [读者条码] as DZCODE  ,");
            strSql.AppendLine(@"        [外借时间] as CHECKTIME ,");
            strSql.AppendLine(@"        [应归还时间] as RETURNTIME ,");
            strSql.AppendLine(@"        [主键码] as PRIMARYKEY,");
            strSql.AppendLine(@"        [虚拟库室] as VIRTUALLIBRARYROOM,");
            strSql.AppendLine(@"        [续借次数] as RENEWNUMBER ,");
            strSql.AppendLine(@"        [处理时间] as PROCESSINGTIME ,");
            strSql.AppendLine(@"        [流通库室键码] as LENDKEY ,");
            strSql.AppendLine(@"        [签到键码] as SIGNIN");
            strSql.AppendLine(@" FROM [流通库]");
            strSql.AppendFormat(@" WHERE [读者条码]='{0}'".FormatWith(dzCode));

            DataTable dt = SqlServerHelper.ExecuteDataTable(strSql.ToString());


            return dt;
        }

        public DataTable GetChangeInfo(string dzCode,string barCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" SELECT [条形码] as BARCODE ,");
            strSql.AppendLine(@"        [登录号] as LOGIN ,");
            strSql.AppendLine(@"        [读者条码] as DZCODE  ,");
            strSql.AppendLine(@"        [外借时间] as CHECKTIME ,");
            strSql.AppendLine(@"        [应归还时间] as RETURNTIME ,");
            strSql.AppendLine(@"        [主键码] as PRIMARYKEY,");
            strSql.AppendLine(@"        [虚拟库室] as VIRTUALLIBRARYROOM,");
            strSql.AppendLine(@"        [续借次数] as RENEWNUMBER ,");
            strSql.AppendLine(@"        [处理时间] as PROCESSINGTIME ,");
            strSql.AppendLine(@"        [流通库室键码] as LENDKEY ,");
            strSql.AppendLine(@"        [签到键码] as SIGNIN");
            strSql.AppendLine(@" FROM [流通库]");
            strSql.AppendFormat(@" WHERE [读者条码]='{0}'".FormatWith(dzCode));
            strSql.AppendFormat(@" AND [条形码]='{0}'".FormatWith(barCode));

            DataTable dt = SqlServerHelper.ExecuteDataTable(strSql.ToString());


            return dt;
        }

        public DataTable GetBackLendLogByDzCode(string dz_code)
        {
            var sqlStr = @"select b.题名 as title,b.责任者 as author,a.条形码 as bookcode,a.处理时间 as processingtime,c.单位 
	                        from 流通日志 a left join 馆藏书目库 b on a.主键码 = b.主键码 left join 读者库 c on a.读者条码 = c.读者条码 
                                where a.读者条码 = '{0}' and a.操作类型 = 'H'".FormatWith(dz_code);
            DataTable dt = SqlServerHelper.ExecuteDataTable(sqlStr.ToString());
            
            return dt;
        }

        public bool UpdateChangeKUReBackTime(string barCode, DateTime time,int reBorrowNum)
        {
            string sql = @"update 流通库 set 应归还时间 ='{0}',续借次数={1}  where 条形码 = '{2}'".FormatWith(time,reBorrowNum, barCode);
            bool flag = SqlServerHelper.ExecuteNonQuery(sql.ToString()) > 0;

            return flag;
        }

        public bool DeleteChangeInfoByBarCode(string barCode)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendLine(@" DELETE FROM 流通库");
            sqlStr.AppendFormat(@" WHERE 条形码='{0}'".FormatWith(barCode));

           

            bool deleteCmd = SqlServerHelper.ExecuteNonQuery(sqlStr.ToString()) > 0;

            
            //删除新增的流通日志成功,但是却没有操作成功
            if (deleteCmd)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int GetOnceBorrowCount(string rdid)
        {
            StringBuilder strSql = new StringBuilder();

            //查询当前已续借次数
            strSql.AppendLine(@" SELECT COUNT(*) FROM 流通日志");
            strSql.AppendFormat(@" WHERE 读者条码='{0}'".FormatWith(rdid));
            strSql.AppendLine(@" AND 操作类型='X'");

            return SqlServerHelper.getSingleInt(strSql.ToString());

        }

        /// <summary>
        /// 根据条形码查询书籍
        /// </summary>
        /// <returns></returns>
        public LocalBooks GetBookByBarCode(string barCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" SELECT c.条形码 as BarCode,");
            strSql.AppendLine(@"        a.题名 as BName,");
            strSql.AppendLine(@"        a.marc as MARC,");
            strSql.AppendLine(@"        a.责任者 as Writer,");
            strSql.AppendLine(@"        a.出版者 as Press,");
            strSql.AppendLine(@"        a.出版日期 as PublicationDate,");
            strSql.AppendLine(@"        a.索书号 as Classify");
            strSql.AppendLine(@" FROM 馆藏书目库 a");
            strSql.AppendLine(@" LEFT JOIN 采购库 b ON a.主键码 = b.主键码");
            strSql.AppendLine(@" LEFT JOIN 馆藏典藏库 c ON b.子键码 = c.子键码");
            strSql.AppendLine(@" WHERE 条形码 ='{0}'".FormatWith(barCode));

            DataTable dt = SqlServerHelper.ExecuteDataTable(strSql.ToString());

            IList<LocalBooks> list = DataTable2List<LocalBooks>.ConvertToModel(dt);

            if (list.Count > 0 && list != null)
            {
                return list[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 流通库插入数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public Boolean AddCirculation(ChangeKuInfo model)
        {
            StringBuilder str = new StringBuilder();
            str.AppendLine(@"INSERT INTO [流通库]
                   ([条形码]
                   ,[登录号]
                   ,[读者条码]
                   ,[外借时间]
                   ,[应归还时间]
                   ,[主键码]
                   ,[虚拟库室]
                   ,[续借次数])
             VALUES ");
            str.AppendFormat(@" ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7})", model.BarCode, model.LoginAccount, model.ReaderCode, model.BorrowTime, model.ShouldReturnTime,
                    model.ID, 1, model.OnceBorrowCount);
            string ss = str.ToString();
            int i = SqlServerHelper.ExecuteNonQuery(str.ToString());
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据UserId获取用户已经借阅的信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetCirculationByUserId(string userId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" SELECT [条形码] as BarCode,");
            strSql.AppendLine(@"        [登录号] as LoginAccount,");
            strSql.AppendLine(@"        [读者条码] as ReaderCode,");
            strSql.AppendLine(@"        [外借时间] as BorrowTime,");
            strSql.AppendLine(@"        [应归还时间] as ShouldReturnTime,");
            strSql.AppendLine(@"        [主键码] as ID,");
            strSql.AppendLine(@"        [虚拟库室] as DummyKu,");
            strSql.AppendLine(@"        [续借次数] as OnceBorrowCount,");
            strSql.AppendLine(@"        [处理时间] as DealTime,");
            strSql.AppendLine(@"        [流通库室键码] as LTKSID,");
            strSql.AppendLine(@"        [签到键码] as QDID");
            strSql.AppendLine(@" FROM [流通库]");
            strSql.AppendLine(@" WHERE [读者条码]='{0}'".FormatWith(userId));

            return SqlServerHelper.ExecuteDataTable(strSql.ToString());
        }

        public string getMarc(string barCode) {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@" SELECT a.marc");
            strSql.AppendLine(@" FROM 馆藏书目库 a");
            strSql.AppendLine(@" LEFT JOIN 采购库 b ON a.主键码 = b.主键码");
            strSql.AppendLine(@" LEFT JOIN 馆藏典藏库 c ON b.子键码 = c.子键码");
            strSql.AppendLine(@" WHERE 条形码 ='{0}'".FormatWith(barCode));
            var dt = SqlServerHelper.ExecuteDataTable(strSql.ToString());
            return dt.Rows[0][0].ToString();
        }

        /// <summary>
        /// 微信书籍分页查询
        /// </summary>
        /// <param name="searchType"></param>
        /// <param name="searchStr"></param>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataTable wxGetNewBooksInfoByPage(string searchType, string searchStr, int page, int pageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY b.题名 ASC) AS rowid, b.索书号 as CALLNUMBER,b.题名 as TITLE,b.责任者 as AUTHOR,
	b.出版者 as PUBLISHER,b.出版地 as PUBLISHERPLACE, b.出版日期 as PUBDATE,b.标准编码 as ISBN
	FROM 
	馆藏书目库 as b  ");
            if (searchType == "1")
            {
                strSql.AppendLine(@" where b.题名 like '%{0}%'".FormatWith(searchStr));
            }
            else if (searchType == "2")
            {
                strSql.AppendLine(@" where b.责任者 like '%{0}%'".FormatWith(searchStr));
            }
            else if (searchType == "3")
            {
                strSql.AppendLine(@" where  b.标准编码 like '%{0}%'".FormatWith(searchStr));
            }

            strSql.AppendLine(@" )t WHERE t.rowid > {0} AND t.rowid <= {1}".FormatWith((page - 1) * pageSize, page * pageSize));

            DataTable dt = SqlServerHelper.ExecuteDataTable(strSql.ToString());

            return dt;
        }
        /// <summary>
        /// 统计微信分页查询的总条数
        /// </summary>
        /// <param name="searchType"></param>
        /// <param name="searchStr"></param>
        /// <returns></returns>
        public int wxSearchCount(string searchType, string searchStr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(@"SELECT Count(*) FROM 馆藏书目库 as b  where");
            if (searchType == "1")
            {
                strSql.AppendLine(@" b.题名 like '%{0}%'".FormatWith(searchStr));
            }
            else if (searchType == "2")
            {
                strSql.AppendLine(@" b.责任者 like '%{0}%'".FormatWith(searchStr));
            }
            else if (searchType == "3")
            {
                strSql.AppendLine(@"  b.标准编码 like '%{0}%'".FormatWith(searchStr));
            }
            

            int count = SqlServerHelper.getSingleInt(strSql.ToString());

            return count;
        }

        /// <summary>
        /// 查询借阅记录 
        /// </summary>
        /// <param name="code">0-当前借阅，1.历史借阅</param>
        /// <param name="dz_code"></param>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataTable wxGetUserLendByPage(string code,string dz_code,int page,int pageSize)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                if (code == "0")
                {
                    sql.AppendLine("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY c.外借时间 ASC) AS rowid, b.题名 AS TITLE,b.责任者 AS AUTHOR,b.出版者 AS PUBLISHER,b.出版地 AS PUBLISHERPLACE,b.出版日期 AS PUBDATE,b.标准编码 AS ISBN,a.馆藏地址 AS COLLETION_ADD, c.条形码 AS    BARCODE, a.状态 AS BOOKSTATE, a.刊价 AS PRICE, a.索书号 AS CALLNUMBER, c.外借时间 AS BORROWDATE, c.应归还时间 AS RETURNDATE FROM 流通库 c left join 馆藏典藏库 as a on c.条形码 = a.条形码 left join 馆藏书目库 as b on a.主键码 = b.主键码 where c.读者条码 = '{0}')t WHERE t.rowid > {1} AND t.rowid <= {2}".FormatWith(dz_code, (page - 1) * pageSize, page * pageSize));
                }
                else
                {
                    sql.AppendLine(" SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY c.归还时间 ASC) AS rowid,b.题名 AS TITLE,b.责任者 AS AUTHOR,b.出版者 AS PUBLISHER,b.出版地 AS PUBLISHERPLACE, b.出版日期 AS PUBDATE, b.标准编码 AS ISBN, a.馆藏地址 AS COLLETION_ADD, c.条形码 AS BARCODE, a.状态 AS BOOKSTATE, a.刊价 AS PRICE, a.索书号 AS CALLNUMBER,c.借书时间 AS BORROWDATE, c.归还时间 AS RETURNDATE FROM (select z.条形码,z.读者条码,z.归还时间,MAX(z.借书时间) 借书时间 from (select x.条形码,x.读者条码,y.处理时间 借书时间,x.处理时间 归还时间 from 流通日志 x left join 流通日志 y on x.条形码 = y.条形码 and x.读者条码 = y.读者条码 and x.处理时间 > y.处理时间 and y.操作类型 = 'J' where x.读者条码 = '{0}' and x.操作类型 = 'H') z group by z.条形码,z.读者条码,z.归还时间) c left join 馆藏典藏库 as a on c.条形码 = a.条形码 left join 馆藏书目库 as b on a.主键码 = b.主键码 )t WHERE t.rowid > {1} AND t.rowid <= {2} ".FormatWith(dz_code, (page - 1) * pageSize, page * pageSize));
                }
                string sqlstr = sql.ToString();
                DataTable dt = SqlServerHelper.ExecuteDataTable(sql.ToString());

                return dt;
            }catch(Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 读者借阅记录统计
        /// </summary>
        /// <param name="code"></param>
        /// <param name="dz_code"></param>
        /// <returns></returns>
        public int wxUserLendCount(string code, string dz_code)
        {
            StringBuilder sql = new StringBuilder();
            if (code == "0")
            {
                sql.AppendLine("SELECT Count(*) FROM 流通库 where 读者条码 = '{0}'".FormatWith(dz_code));
            }
            else
            {
                sql.AppendLine(" SELECT Count(*) FROM 流通日志 where 读者条码 = '{0}' AND 操作类型='H' ".FormatWith(dz_code));
            }

            int count = SqlServerHelper.getSingleInt(sql.ToString());
            return count;
        }

        /// <summary>
        /// 新书推荐
        /// </summary>
        /// <param name="Size"></param>
        /// <returns></returns>
        public DataTable wxGetNewBooksBySize(int Size)
        {
            string sql = "SELECT TOP({0}) b.索书号 as CALLNUMBER,b.题名 as TITLE,b.责任者 as AUTHOR, b.出版者 as PUBLISHER,b.出版地 as PUBLISHERPLACE, b.出版日期 as PUBDATE,b.标准编码 as ISBN  FROM 馆藏书目库 b where b.题名 is not null and b.标准编码 is not null order by b.主键码 desc  ".FormatWith(Size);

            DataTable dt = SqlServerHelper.ExecuteDataTable(sql.ToString());

            return dt;
        }
        

        public DataTable GetSIP2LendInfoByBarCode(string barCode)
        {
            string sql = @"select a.主键码,a.读者条码,a.条形码,a.应归还时间,c.题名,d.部门名称,c.索书号,e.单价,f.已外借 from 流通库 a left join 馆藏典藏库 b on a.条形码 = b.条形码 left join 馆藏书目库 c on a.主键码 = c.主键码 left join 馆藏地址定义 d on b.馆藏地址 = d.馆藏地址 left join 采购库 e on b.子键码 = e.子键码 left join 读者库 f on a.读者条码 = f.读者条码 where a.条形码='{0}'".FormatWith(barCode);

            DataTable dt = SqlServerHelper.ExecuteDataTable(sql.ToString());

            return dt;
        }

        public DataTable GetSIP2LendInfoByDzCode(string dzCode)
        {
            string sql = @"select a.主键码,a.读者条码,a.条形码,a.应归还时间,c.题名,d.部门名称,c.索书号,e.单价,f.已外借 from 流通库 a left join 馆藏典藏库 b on a.条形码 = b.条形码 left join 馆藏书目库 c on a.主键码 = c.主键码 left join 馆藏地址定义 d on b.馆藏地址 = d.馆藏地址 left join 采购库 e on b.子键码 = e.子键码 left join 读者库 f on a.读者条码 = f.读者条码 where a.读者条码='{0}'".FormatWith(dzCode);

            DataTable dt = SqlServerHelper.ExecuteDataTable(sql.ToString());

            return dt;
        }

        public DataTable GetSIP2BookInfoByBarCode(string barCode)
        {
            string sql = @"select a.主键码,a.条形码,d.题名,b.单价,c.允许外借,a.虚拟库室 from 馆藏典藏库 a left join 采购库 b on a.子键码 = b.子键码 left join 馆藏地址定义 c on a.馆藏地址 = c.馆藏地址 left join 馆藏书目库 d on a.主键码 = d.主键码  where 条形码='{0}'".FormatWith(barCode);

            DataTable dt = SqlServerHelper.ExecuteDataTable(sql.ToString());

            return dt;
        }

        /// <summary>
        /// 获取流通规则
        /// </summary>
        /// <param name="xnks"></param>
        /// <param name="jb"></param>
        /// <param name="dwdm"></param>
        /// <returns></returns>
        public DataTable GetcirculationParmar(string xnks,string jb,string dwdm)
        {
            string sql = @"select * from 流通参数定义 where 虚拟库室 = {0} and 级别代码='{1}' and 单位代码='{2}'".FormatWith(xnks,jb,dwdm);

            DataTable dt = SqlServerHelper.ExecuteDataTable(sql.ToString());

            return dt;
        }

        public DataTable GetSip2BookDescByBarCode(string barCode)
        {
            string sql = @"select e.单价 as CH,a.虚拟库室 as CT,a.馆藏地址 as AQ ,b.题名 as AJ,b.责任者 AS AW,b.标准编码 AS AK,b.索书号 AS KC,b.出版者 AS PB,ISNULL(c.读者条码 ,'')AS BG,ISNULL(d.读者条码 ,'') AS AA,ISNULL(D.应归还时间 ,'') AS AH from 馆藏典藏库 a left join 馆藏书目库 b on a.主键码 = b.主键码 left join 预约库 c on a.主键码 = c.主键码 left join 流通库 d on a.条形码 = d.条形码 left join 采购库 e on a.主键码 = e.主键码 and a.子键码 = e.子键码 where a.条形码 = '{0}'".FormatWith(barCode);
            DataTable dt = SqlServerHelper.ExecuteDataTable(sql.ToString());

            return dt;
        }

        public DataTable GetSipLendBookPriceSUM(string dzCode)
        {
            string sql = @"select sum(e.单价) as price from 流通库 a left join 馆藏典藏库 b on a.条形码 = b.条形码  left join 采购库 e on b.子键码 = e.子键码  where  a.读者条码='{0}'".FormatWith(dzCode);

            DataTable dt = SqlServerHelper.ExecuteDataTable(sql.ToString());

            return dt;
        }
    }
}
